package com.cn.lyl.lawyer.util;
import java.sql.*;

/**
 * 访问数据库的步骤
 * 1.加载驱动   mysql驱动--jar包--什么时候加载？
 * 2.取得数据库链接
 * 3.封装sql语句（编译sql语句）
 * 4.执行sql语句
 * 5.返回结果
 *
 * PreparedStatement和statement的区别？
 */
public class JDBC {

    //    反射  通过反射拿到对象
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 取得数据库链接
     *
     * @return
     */
    private static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/lawyer_firm?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&serverTimezone=Asia/Shanghai", "root", "");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     *
     * @param sql
     * @return 返回影响数据的条数
     */
    public static int update(String sql) {
        PreparedStatement preparedStatement = null;
        Connection connection = getConnection();
//        Statement PreparedStatement的区别？
        try {
//            编译sql语句

            preparedStatement = connection.prepareStatement(sql);
//            返回的影响数据的条数
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }


    public static ResultSet query(String sql) {
        PreparedStatement preparedStatement = null;
        Connection connection = getConnection();
        try {
            preparedStatement = getConnection().prepareStatement(sql);

            return preparedStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public static int insert(String sqlInsert,String sqlInsertId,String dependenceId,String dependenceTable,String idName,String dIdName){
        PreparedStatement preparedStatement = null;
        Connection connection = getConnection();
        int id_i = 0;//主表id
        int id_d = 0;//联合表id
        try {
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sqlInsert);
            preparedStatement.executeUpdate();
            ResultSet query1 = query(sqlInsertId);
            if(query1.next()){
              id_i =  query1.getInt("id");
            }
            ResultSet query2 = query(dependenceId);
            if(query2.next()){
                id_d =  query1.getInt("id");
            }
            if(id_d!=0&&id_i!=0){
                String sql_d ="INSERT INTO "+dependenceTable+" ("+idName+","+dIdName+",create_time, update_time, enable, remark)" +
                        "VALUES ("+id_i+","+id_d+",now(),now(),1,'')";



                preparedStatement.executeUpdate(sql_d);
                connection.commit();
                System.out.println(sql_d);
                return 1;

            }
            else{
                connection.rollback();
                connection.commit();
                return 0;
            }

        } catch (Exception e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            try {
                connection.commit();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return 0;
        }
    }


    public static int delete (String sqlDelete,String sqlDependence){
        PreparedStatement preparedStatement = null;
        Connection connection = getConnection();
        try {
            connection.setAutoCommit(false);
            preparedStatement.executeUpdate(sqlDependence);
            preparedStatement.executeUpdate(sqlDelete);
            connection.commit();
            return 1;
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            try {
                connection.commit();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
        return 0;
    }
}